﻿using LY.Entity;
using LY.Entity.Entities;
using LY.Entity.PageSearch;
using LY.Entity.SearchEntity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LY.DataAccess.Repository
{
    public class ArticleRepository : BaseRepository
    {
        /// <summary>
        /// 根据主键ID检索文章信息,返回单个实体对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public C_Article GetArticle(int? id)
        {
            string sql = @" SELECT [ID]
                      ,[Name]
                      ,[Keywords]
                      ,[Author]
                      ,[Synopsis]
                      ,[Content]
                      ,[TxtContent]
                      ,[Hits]
                      ,[CreateTime]
                      ,[CreateUser]
                      ,[CreateUserID]
                      ,[PublishTime]
                      ,[PublishUser]
                      ,[PublishUserID]
                      ,[UpdateTime]
                      ,[IsTop]
                      ,[Source]
                      ,[Status]
                      ,[Sequence]
                      ,[URL]
                      ,[MediaName]
                      ,[MediaURL]
                      ,[Pictures]
                      ,[PicturesPath]
                      ,[Files]
                      ,[FilesPath]
                      ,[Flashs]
                      ,[FlashsPath]
                      ,[TitlePic]
                      ,[Vedios]
                      ,[VedioPath]
                      ,[Menu]
                      ,[MenuId]
                       FROM [C_Article] WHERE Id=@ID";
            return base.Get<C_Article>(sql, new { ID = id });
        }

        /// <summary>
        /// 根据主键ID检索文章信息,返回单个实体对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public C_Article GetArticleByMenu(int? menuid)
        {
            string sql = @" SELECT top 1 [ID]
                      ,[Name]
                      ,[Keywords]
                      ,[Author]
                      ,[Synopsis]
                      ,[Content]
                      ,[TxtContent]
                      ,[Hits]
                      ,[CreateTime]
                      ,[CreateUser]
                      ,[CreateUserID]
                      ,[PublishTime]
                      ,[PublishUser]
                      ,[PublishUserID]
                      ,[UpdateTime]
                      ,[IsTop]
                      ,[Source]
                      ,[Status]
                      ,[Sequence]
                      ,[URL]
                      ,[MediaName]
                      ,[MediaURL]
                      ,[Pictures]
                      ,[PicturesPath]
                      ,[Files]
                      ,[FilesPath]
                      ,[Flashs]
                      ,[FlashsPath]
                      ,[TitlePic]
                      ,[Vedios]
                      ,[VedioPath]
                      ,[Menu]
                      ,[MenuId]
                       FROM [C_Article] WHERE MenuId=@ID order by CreateTime desc ";
            return base.Get<C_Article>(sql, new { ID = menuid });
        }

        /// <summary>
        /// 根据主键ID检索文章信息,返回单个实体对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<C_Article> GetArticleListByMenu(int? menuid)
        {
            string sql = @" SELECT [ID]
                      ,[Name]
                      ,[Keywords]
                      ,[Author]
                      ,[Synopsis]
                      ,[Content]
                      ,[TxtContent]
                      ,[Hits]
                      ,[CreateTime]
                      ,[CreateUser]
                      ,[CreateUserID]
                      ,[PublishTime]
                      ,[PublishUser]
                      ,[PublishUserID]
                      ,[UpdateTime]
                      ,[IsTop]
                      ,[Source]
                      ,[Status]
                      ,[Sequence]
                      ,[URL]
                      ,[MediaName]
                      ,[MediaURL]
                      ,[Pictures]
                      ,[PicturesPath]
                      ,[Files]
                      ,[FilesPath]
                      ,[Flashs]
                      ,[FlashsPath]
                      ,[TitlePic]
                      ,[Vedios]
                      ,[VedioPath]
                      ,[Menu]
                      ,[MenuId]
                       FROM [C_Article] WHERE MenuId=@ID order by CreateTime desc ";
            return base.Query<C_Article>(sql, new { ID = menuid });
        }

        /// <summary>
        /// 根据主键ID检索文章信息,返回单个实体对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int GetArticleCountByMenu(int? menuid)
        {
            string sql = @" SELECT Count([ID])
                       FROM [C_Article] WHERE MenuId=@ID ";
            return base.Get<int>(sql, new { ID = menuid });
        }
        /// <summary>
        /// 根据主键ID检索栏目信息,返回单个实体对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public C_Menu GetMenu(int? id)
        {
            string sql = @" SELECT  [ID]
                          ,[Name]
                          ,[UpperID]
                          ,[UpperName]
                          ,[Code]
                          ,[Path]
                          ,[Sequence]
                          ,[URL]
                          ,[Status]
                          ,[NameE]
                          ,[Target]
                        ,[Layer] 
                       FROM [C_Menu] WHERE Id=@ID";
            return base.Get<C_Menu>(sql, new { ID = id });
        }
        /// <summary>
        /// 根据相应条件查询栏目信息，返回数据集合
        /// </summary>
        /// <param name="search"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        public PagedList<C_Menu> QueryMenuList(ArticleSearch search, PageView view)
        {
            string cols = @" [ID]
                          ,[Name]
                          ,[UpperID]
                          ,[UpperName]
                          ,[Code]
                          ,[Path]
                          ,[Sequence]
                          ,[URL]
                          ,[Status]
                          ,[NameE]
                          ,[Target]
                            ,Layer";
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += "AND [Name] LIKE '%" + search.Name + "%' ";
            }
            sqlWhere += "AND id <> 170 ";

            //return base.PageGet<C_Menu>(view, cols, @" [C_Menu]", sqlWhere, " [Sequence]", "");
            return base.PageGet<C_Menu>(view, cols, @" [C_Menu]", sqlWhere, " [Path]", "");
        }
        /// <summary>
        /// 根据栏目ID查询子栏目信息，返回数据集合
        /// </summary>
        /// <param name="search"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        public PagedList<C_Menu> QueryMenuListId(int? Id, ArticleSearch search, PageView view)
        {
            string cols = @" [ID]
                          ,[Name]
                          ,[UpperID]
                          ,[UpperName]
                          ,[Code]
                          ,[Path]
                          ,[Sequence]
                          ,[URL]
                          ,[Status]
                          ,[NameE]
                          ,[Target]
                            ,Layer";

            string sqlWhere = "";
            if (Id.HasValue)
            {
                sqlWhere += "AND [UpperID] =" + Id + "";
            }
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += "AND [Name] LIKE '%" + search.Name + "%'";
            }
            return base.PageGet<C_Menu>(view, cols, @" [C_Menu]", sqlWhere, " [Sequence]", "");
        }
        /// <summary>
        /// 根据相应条件查询文章信息，返回数据集合
        /// </summary>
        /// <param name="search"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        public PagedList<C_Article> QueryArticleList(ArticleSearch search, PageView view)
        {
            string cols = @" [ID]
                          ,[Name]
                          ,[Keywords]
                          ,[Author]
                          ,[Synopsis]
                          ,[Content]
                          ,[TxtContent]
                          ,[Hits]
                          ,[CreateTime]
                          ,[CreateUser]
                          ,[CreateUserID]
                          ,[PublishTime]
                          ,[PublishUser]
                          ,[PublishUserID]
                          ,[UpdateTime]
                          ,[IsTop]
                          ,[Source]
                          ,[Status]
                          ,[Sequence]
                          ,[URL]
                          ,[MediaName]
                          ,[MediaURL]
                          ,[Pictures]
                          ,[PicturesPath]
                          ,[Files]
                          ,[FilesPath]
                          ,[Flashs]
                          ,[FlashsPath]
                          ,[TitlePic]
                          ,[Vedios]
                          ,[VedioPath]
                          ,[Menu]
                          ,[MenuId]";
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(search.Name) && string.IsNullOrEmpty(search.PublishTime))
            {
                //DateTime data = Convert.ToDateTime(search.PublishTime);
                sqlWhere += " AND [Name] LIKE '%" + search.Name + "%' or Author like '%" + search.Name + "%' ";
            }
            if (!string.IsNullOrEmpty(search.PublishTime) && string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND [PublishTime]='" + search.PublishTime + "'";
            }
            if (!string.IsNullOrEmpty(search.Name) && !string.IsNullOrEmpty(search.PublishTime))
            {
                sqlWhere += " AND [Name] LIKE '%" + search.Name + "%' or Author like '%" + search.Name + "%' or PublishTime='" + search.PublishTime + "'";
            } 
            if (!string.IsNullOrEmpty(search.MenuId))
            {
                sqlWhere += " AND [MenuId]='" + search.MenuId + "'";
            }
            return base.PageGet<C_Article>(view, cols,
                @" [C_Article] "
                , sqlWhere
                , " [IsTop] desc,[Sequence] ",
                "");
        }
        /// <summary>
        /// 删除文章信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteArticle(int id)
        {
            string strSql = " Delete from C_Article where ID=@ID";
            return base.Excute(strSql, new { ID = id });
        }
        /// <summary>
        /// 删除栏目信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteMenu(int id)
        {
            string strSql = " Delete from C_Menu where ID=@ID";
            return base.Excute(strSql, new { ID = id });
        }
        //查找父级ID是否存在
        public int QueryUpperID(int id)
        {
            string strSql = " Select COUNT(*) from C_Menu where UpperID=@ID";
            return base.Get<int>(strSql, new { ID = id });
        }

        public List<C_Menu> GetUpperMenuList(int? id)
        {
            string sql = @" SELECT [ID]
                          ,[Name]
                          ,[UpperID]
                          ,[UpperName]
                          ,[Code]
                          ,[Path]
                          ,[Sequence]
                          ,[URL]
                          ,[Status]
                          ,[NameE]
                          ,[Target]
                       FROM [C_Menu] WHERE [UpperID]=@ID";
            return base.Query<C_Menu>(sql, new { ID = id });
        }

        //检查Code是否重复
        public int GetCode(string code)
        {
            string sql = @" select COUNT(1) from C_Menu where Code=@code";
            return base.Get<int>(sql, new { code = code });
        }
        //修改时检查Code是否重复
        public int GetCode(int id, string code)
        {
            string sql = @" select count(1) from (select * from C_Menu where code not in(select Code from C_Menu where ID=@id))r where Code=@code";
            return base.Get<int>(sql, new { id = id, code = code });
        }
        //根据栏目ID检查当前栏目下是否有文章信息
        public int CheckArticle(int menuid)
        {
            string sql = @" select COUNT(1) from C_Article where MenuId=" + menuid + "";
            return base.Get<int>(sql, new { menuid = menuid });
        }

        //文章资讯 前5
        public List<C_Article> GetArticleList(int menuid, int top)
        {
            string sql =string.Format(@" SELECT TOP {1} [ID]                       
                          ,[Name]
                          ,[Keywords]
                          ,[Author]
                          ,[Synopsis]
                          ,[Content]
                          ,[TxtContent]
                          ,[Hits]
                          ,[CreateTime]
                          ,[CreateUser]
                          ,[CreateUserID]
                          ,[PublishTime]
                          ,[PublishUser]
                          ,[PublishUserID]
                          ,[UpdateTime]
                          ,[IsTop]
                          ,[Source]
                          ,[Status]
                          ,[Sequence]
                          ,[URL]
                          ,[MediaName]
                          ,[MediaURL]
                          ,[Pictures]
                          ,[PicturesPath]
                          ,[Files]
                          ,[FilesPath]
                          ,[Flashs]
                          ,[FlashsPath]
                          ,[TitlePic]
                          ,[Vedios]
                          ,[VedioPath]
                          ,[Menu]
                          ,[MenuId]
                       FROM [C_Article] where MenuId = {0} ORDER BY CreateTime DESC",menuid,top);
            return base.Query<C_Article>(sql, null);
        }
        /// <summary>
        /// 所有文章列表（灌粉公告-更多）
        /// </summary>
        /// <param name="search"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        public PagedList<C_Article> QueryList(ArticleSearch search, PageView view)
        {
            string cols = @" [ID]
                          ,[Name],[PublishTime]
                        ";
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(search.Name))
            {
                //DateTime data = Convert.ToDateTime(search.PublishTime);
                sqlWhere += " AND [Name] LIKE '%" + search.Name + "%' or Author like '%" + search.Name + "%' ";
            }
            return base.PageGet<C_Article>(view, cols,
                @" [C_Article] "
                , sqlWhere
                , " [CreateTime] desc ",
                "");
        }

        public int GetArticleCount(int menuid)
        {
            string sql = @" select COUNT(1) from C_Article where MenuId=@menuid";
            return base.Get<int>(sql, new { menuid = menuid });
        }

        /// <summary>
        /// 更新栏目信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpdateSequence(string oldpath, string newpath)
        {
            string sql = "  Update [C_Menu] set path = REPLACE(path,@oldpath,@newpath) WHERE Path like @oldpath + '%'  ";
            return base.Excute(sql, new { oldpath = oldpath, newpath = newpath });
        }

        
            

        /// <summary>
        /// 检索模块信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<C_Menu> GetMenuSub(int? id)
        {
            string sql = @" SELECT ID
                       FROM [C_Menu] WHERE Path like (SELECT Path+'%'
                       FROM [C_Menu] WHERE Id=@ID)";
            return base.Query<C_Menu>(sql, new { ID = id });

        }

        /// <summary>
        /// 根据栏目CODE 检索新闻 
        /// </summary>
        /// <param name="code">金融社区</param>
        /// <param name="top"></param>
        /// <returns></returns>
        public List<C_Article> GetArticleList(string code, int top)
        {
            string sql = string.Format(@" SELECT TOP {1} [ID]                       
                          ,[Name]
                          ,[Keywords]
                          ,[Author]
                          ,[Synopsis]
                          ,[Content]
                          ,[TxtContent]
                          ,[Hits]
                          ,[CreateTime]
                          ,[CreateUser]
                          ,[CreateUserID]
                          ,[PublishTime]
                          ,[PublishUser]
                          ,[PublishUserID]
                          ,[UpdateTime]
                          ,[IsTop]
                          ,[Source]
                          ,[Status]
                          ,[Sequence]
                          ,[URL]
                          ,[MediaName]
                          ,[MediaURL]
                          ,[Pictures]
                          ,[PicturesPath]
                          ,[Files]
                          ,[FilesPath]
                          ,[Flashs]
                          ,[FlashsPath]
                          ,[TitlePic]
                          ,[Vedios]
                          ,[VedioPath]
                          ,[Menu]
                          ,[MenuId]
                       FROM [C_Article] where MenuId =(select ID from C_Menu where Code='{0}') ORDER BY CreateTime DESC", code, top);
            return base.Query<C_Article>(sql, null);
        }
    }
}
